#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
补充台湾省、香港特别行政区、澳门特别行政区行政区划SQL生成脚本
作者：Wu.Liang
日期：2025-06-27
"""

import json
import os
from datetime import datetime

def parse_region(code, node, parent_code, level, region_type, values):
    """递归解析区域数据"""
    # 构建区域记录
    region_record = {
        'id': code,
        'region_name': node['name'],
        'region_code': code,
        'parent_id': parent_code,
        'region_type': region_type,
        'level': level,
        'manager_id': None,
        'description': node['name'],
        'address': None,
        'longitude': None,
        'latitude': None,
        'area': None,
        'population': 0,
        'community_count': 0,
        'contact_phone': None,
        'contact_email': None,
        'postal_code': None,
        'status': 1,
        'sort_order': 0,
        'create_by': 1,
        'create_time': 'NOW()',
        'update_by': None,
        'update_time': 'NOW()',
        'deleted': 0,
        'version': 1,
        'region_level': level
    }
    
    values.append(region_record)
    
    # 递归处理子区域
    if 'children' in node and node['children']:
        next_level = level + 1
        next_type = region_type + 1 if region_type < 4 else 4
        for sub_node in node['children']:
            parse_region(sub_node['code'], sub_node, code, next_level, next_type, values)

def generate_sql_for_region(region_code, region_name, data):
    """为指定区域生成SQL文件"""
    print(f"正在生成 {region_name} 的SQL文件...")
    
    # 解析数据
    values = []
    parse_region(region_code, data, '0', 1, 1, values)
    
    # 生成SQL文件
    sql_filename = f"province_{region_code}_{region_name}.sql"
    sql_path = os.path.join("AI_Task/区域管理测试数据补充_执行文档/province_sqls", sql_filename)
    
    with open(sql_path, 'w', encoding='utf-8') as f:
        f.write(f"-- {region_name} 行政区划SQL\n")
        f.write(f"-- 作者：Wu.Liang\n")
        f.write(f"-- 日期：{datetime.now().strftime('%Y-%m-%d %H:%M:%S')}\n\n")
        
        f.write("TRUNCATE TABLE `sc_region`;\n\n")
        
        f.write("INSERT INTO `sc_region` (\n")
        f.write("  `id`, `region_name`, `region_code`, `parent_id`, `region_type`, `level`,\n")
        f.write("  `manager_id`, `description`, `address`, `longitude`, `latitude`, `area`,\n")
        f.write("  `population`, `community_count`, `contact_phone`, `contact_email`, `postal_code`,\n")
        f.write("  `status`, `sort_order`, `create_by`, `create_time`, `update_by`, `update_time`,\n")
        f.write("  `deleted`, `version`, `region_level`\n")
        f.write(") VALUES\n")
        
        # 写入数据
        for i, record in enumerate(values):
            f.write("(")
            f.write(f"'{record['id']}', '{record['region_name']}', '{record['region_code']}', '{record['parent_id']}', {record['region_type']}, {record['level']}, ")
            f.write(f"{record['manager_id'] or 'NULL'}, '{record['description']}', {record['address'] or 'NULL'}, {record['longitude'] or 'NULL'}, {record['latitude'] or 'NULL'}, {record['area'] or 'NULL'}, ")
            f.write(f"{record['population']}, {record['community_count']}, {record['contact_phone'] or 'NULL'}, {record['contact_email'] or 'NULL'}, {record['postal_code'] or 'NULL'}, ")
            f.write(f"{record['status']}, {record['sort_order']}, {record['create_by']}, {record['create_time']}, {record['update_by'] or 'NULL'}, {record['update_time']}, ")
            f.write(f"{record['deleted']}, {record['version']}, {record['region_level']}")
            f.write(")")
            
            if i < len(values) - 1:
                f.write(",\n")
            else:
                f.write(";\n")
    
    print(f"✅ {region_name} SQL文件生成完成: {sql_filename}")
    return len(values)

def main():
    """主函数"""
    print("开始生成台湾省、香港特别行政区、澳门特别行政区行政区划SQL...")
    
    # 确保目录存在
    os.makedirs("AI_Task/区域管理测试数据补充_执行文档/province_sqls", exist_ok=True)
    
    # 读取数据
    try:
        with open("AI_Task/区域管理测试数据补充_执行文档/taiwan_hk_macau_data.json", 'r', encoding='utf-8') as f:
            data = json.load(f)
    except FileNotFoundError:
        print("❌ 数据文件不存在，请先创建 taiwan_hk_macau_data.json")
        return
    
    # 生成各区域SQL
    total_records = 0
    regions = [
        ("71", "台湾省", data["71"]),
        ("81", "香港特别行政区", data["81"]),
        ("82", "澳门特别行政区", data["82"])
    ]
    
    for region_code, region_name, region_data in regions:
        record_count = generate_sql_for_region(region_code, region_name, region_data)
        total_records += record_count
        print(f"📊 {region_name}: {record_count} 条记录")
    
    print(f"\n🎉 所有SQL文件生成完成！")
    print(f"📈 总计生成记录数: {total_records}")
    print(f"📁 文件位置: AI_Task/区域管理测试数据补充_执行文档/province_sqls/")

if __name__ == "__main__":
    main() 